﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace RMSSever
{
    public static class SQL
    {
        // 类中定义方法均为 静态的 （全局方法），不需实例化，直接以类名调用即可

        public static string server = ".";
        public static string database = "RMS";
        public static string uid = "sa";
        public static string pwd = "sa";
        public static string SqlConnString = "server=" + server + ";database=" + database + ";uid=" + uid + ";pwd=" + pwd;

            
        //查询SQL语句执行之后是否返回了一行或多行，若有返回则函数返回1，否则返回0，若发生错误则返回404
        public static int ReturnRows(string Sql)
        {
            try
            {
                SqlConnection conn = new SqlConnection(SqlConnString);
                conn.Open();
                SqlCommand cmd = new SqlCommand(Sql, conn);
                SqlDataReader sdr = cmd.ExecuteReader();
                sdr.Read();
                if (sdr.HasRows)
                {
                    sdr.Close();
                    conn.Close();
                    return 1;
                }
                else
                {
                    sdr.Close();
                    conn.Close();
                    return 0;
                }

            }
            catch (Exception exp)
            {
                MessageBox.Show(exp.ToString());
                return 404;
            }
        }


        //SQL语句执行之后返回一条记录中的一个值，若发生错误或者没有符合条件的记录则返回"有错误发生！"
        public static string ReturnOneData(string Sql)
        {
            try
            {
                string rData;   // 声明返回值
                SqlConnection conn = new SqlConnection(SqlConnString);
                conn.Open();
                SqlCommand cmd = new SqlCommand(Sql, conn);
                rData = cmd.ExecuteScalar().ToString();
                conn.Close();
                return rData;
            }
            catch (Exception exp)
            {
                MessageBox.Show(exp.ToString());
                return "发生错误";
            }
        }


        //SQL语句执行之后返回一个DataTable对象，即返回一个表
        public static DataTable ReturnOneDataTable(string Sql)
        {
            DataTable dt = new DataTable();
            try
            {
                SqlConnection conn = new SqlConnection("server=.;database=AIDSLab;uid=sa;pwd=sa");
                conn.Open();
                SqlCommand cmd = new SqlCommand(Sql, conn);
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                sda.Fill(dt);
                conn.Close();
            }
            catch (Exception exp)
            {
                MessageBox.Show(exp.ToString());
            }
            return dt;
        }



        /// <summary>
        /// SQL更新语句执行之后返回一个bool值，语句执行成功返回true，失败返回false
        /// </summary>
        /// <param name="Sql">将要执行的SQL语句</param>
        /// <returns>语句执行成功返回true，失败返回false</returns>
        public static bool Update(string Sql)
        {
            bool r = false;   // 声明返回值，默认置为false

            try
            {
                SqlConnection conn = new SqlConnection(SqlConnString);
                conn.Open();
                SqlCommand cmd = new SqlCommand(Sql, conn);

                // count就是受影响的行数,如果count>0说明执行成功,如果count=0说明没有成功.
                int count = cmd.ExecuteNonQuery();
                if (count > 0)
                {
                    r = true;
                }
                else
                {
                    r = false;
                }
                conn.Close();
                return r;

            }
            catch (Exception exp)
            {
                MessageBox.Show("Update命令执行发生错误！\r\n" + exp.ToString(), "错误", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return r;
            }
        }


        /// <summary>
        /// SQL插入语句执行之后返回一个bool值，插入执行成功返回true，失败返回false
        /// </summary>
        /// <param name="Sql">将要执行的SQL插入语句</param>
        /// <returns>插入执行成功返回true，失败返回false</returns>
        public static bool Insert(string Sql)
        {
            bool r = false;   // 声明返回值，默认置为false

            try
            {
                SqlConnection conn = new SqlConnection(SqlConnString);
                conn.Open();
                SqlCommand cmd = new SqlCommand(Sql, conn);

                // count就是受影响的行数,如果count>0说明执行成功,如果=0说明没有成功.
                int count = cmd.ExecuteNonQuery();
                if (count > 0)
                {
                    r = true;
                }
                else
                {
                    r = false;
                }
                conn.Close();
                return r;
            }
            catch (Exception exp)
            {
                MessageBox.Show("Update命令执行发生错误！\r\n" + exp.ToString(), "错误", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return r;
            }
        }

    }
}
